﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using QuanLyNhaSach.QuanLyNhaSachDTO;

namespace QuanLyNhaSach.QuanLyNhaSachDao
{
    class ChiTietHoaDonDao
    {
        public static DataTable GetChiTietHoaDonAll()
        {
            string sql = "select *from ChiTietHoaDon where Active=True";
            return Database.ExcuQuery(sql);
        }

        public static DataTable GetChiTietHoaDonTheoMaHD(int mahd)
        {
            string sql = "select * from ChiTietHoaDon where Active=True And MaHoaDon = " + mahd + "";
            return Database.ExcuQuery(sql);
        }

        public static bool Insert(ChiTietHoaDonDTO tl)
        {
            string sql;
            bool active = true;
            sql = "insert into ChiTietHoaDon(MaSach,MaHoaDon,SoLuongMua,Active) values('" + tl.MaSach + "','" + tl.MaHoaDon + "'," + tl.SoLuongMua + "," + active + ")";
            Database.ExcuNonQuery(sql);
            return true;

        }
        public static void Delete(ChiTietHoaDonDTO tl)
        {
            // bool active = false;
            string sql = "Delete from ChiTietHoaDon where MaChiTietHoaDon=" + tl.MaChiTietHoaDon + "";
            Database.ExcuNonQuery(sql);
        }
        public static bool Update(ChiTietHoaDonDTO s)
        {
            string sql = "update  ChiTietHoaDon set MaSach=('" + s.MaSach + "'),MaHoaDon=('" + s.MaHoaDon + "'),SoLuongMua=(" + s.SoLuongMua + ") where MaChiTietHoaDon = " + s.MaChiTietHoaDon + "";

            Database.ExcuNonQuery(sql);

            return true;
        }
        public static void UpdateSoLuongSach(ChiTietHoaDonDTO s)
        {
            string sql = "Update  Sach set SoLuongTon= SoLuongTon - (" + s.SoLuongMua + ") where MaSach = " + s.MaSach + "";

            Database.ExcuNonQuery(sql);

        }
        public static int SoLuongTon(ChiTietHoaDonDTO pn)
        {

            string sql = "select SoLuongTon from Sach where MaSach=" + pn.MaSach + "";
            DataTable dt = Database.ExcuQuery(sql);
            int e = Convert.ToInt32(dt.Rows[0]["SoLuongTon"].ToString());
            return e;
        }
        public static bool KTSoLuongTon(ChiTietHoaDonDTO pn)
        {
            int e = SoLuongTon(pn);
          
            if (e < 20)
                return false;
            return true;
        }
        public static void UpdateXoa(ChiTietHoaDonDTO s)
        {
            string sql = "Update  Sach set SoLuongTon= SoLuongTon + (" + s.SoLuongMua + ") where MaSach = " + s.MaSach + "";

            Database.ExcuNonQuery(sql);

        }
        public static void UpdateSua(int sl,ChiTietHoaDonDTO s)
        {
            string sql = "update Sach set SoLuongTon  =( " + sl + ") where MaSach = "+s.MaSach+"";
             Database.ExcuNonQuery(sql);
        }
        public static int SoLuongMua(ChiTietHoaDonDTO pn)
        {

            string sql = "select SoLuongMua from ChiTietHoaDon where MaChiTietHoaDon=" + pn.MaChiTietHoaDon + "";
            DataTable dt = Database.ExcuQuery(sql);
            int e = Convert.ToInt32(dt.Rows[0]["SoLuongMua"].ToString());
            return e;
        }
        public static void InsertBaoCaoTon(ChiTietHoaDonDTO pn)
        {
            string sql;
            bool active = true;

            sql = "select* from Sach where Active=True and MaSach=" + Convert.ToInt32(pn.MaSach) + "";
            DataTable dt = Database.ExcuQuery(sql);
            int tonht = 0;
            if (dt.Rows.Count > 0)
                tonht = Convert.ToInt32(dt.Rows[0]["SoLuongTon"].ToString());
            //int tondau = pn.SoLuongNhap - tonht;
            sql = "select* from HoaDon where Active=True and MaHoaDon=" + Convert.ToInt32(pn.MaHoaDon) + "";
            dt = Database.ExcuQuery(sql);
            DateTime t = DateTime.Now;
            if (dt.Rows.Count > 0)
                t = Convert.ToDateTime(dt.Rows[0]["NgayLap"].ToString());

            sql = "insert into BaoCaoTon(MaSach,TonDau,PhatSinh,TonCuoi,Active,NgayPhatSinh) values('" + pn.MaSach + "'," + (tonht + pn.SoLuongMua) + "," + 0 + "," + tonht + "," + active + ",'" + t + "') ";

            Database.ExcuNonQuery(sql);


        }
    }
}
